import pandas as pd
import pymysql
import datetime

db = pymysql.connect(
    host="172.17.9.105",
    user="cs_platform",
    password="cs_platfm_01",
    database="icrg_test",
)
c = db.cursor()
sql_total = f"select source as s,count(*) as c,round(avg(price),2) as a from h_p where crawl_date = '{datetime.date.today()}' group by source"
c.execute(sql_total)
rs = c.fetchall()
data_total = []
for r in rs:
    data_total.append(list(r))
df1 = pd.DataFrame(data_total, columns=["来源", "房源数量(个)", "平均价格(元/㎡)"])

sql_p_a = "select t.* from (select position as p ,count(1) as c ,round(avg(price),2) as pr  from h_p t group by position ) t order by t.pr,t.c"
c.execute(sql_p_a)
rs2 = c.fetchall()
data_position = []
for r in rs2:
    data_position.append(list(r))
df2 = pd.DataFrame(data_position, columns=["小区", "房源数量(个)", "平均价格(元/㎡)"])

with pd.ExcelWriter(f"{datetime.date.today()}武汉房价一览.xlsx") as wr:
    df1.to_excel(wr, sheet_name="总计", index=False)
    df2.to_excel(wr, sheet_name="详情", index=False)


c.close()
db.close()
